﻿using DBUtil.MetaData;
using DBUtil.Provider.SqlServer.MetaData;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace DBUtil.Provider.SqlServer
{
    public partial class SqlServerManage : DBManage
    {
        #region 生成增删改查代码
        /// <summary>
        /// 根据表生成增删改查代码
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="tableName">表名称</param>
        /// <returns></returns>
        public override string GenerateCode(string tableName, string schemaName = null)
        {
            return _generateCode(schemaName, tableName);
        }

        /// <summary>
        /// 根据视图的实体代码
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="viewName">视图名称</param>
        /// <returns></returns>
        public string GenerateViewCode(string viewName, string schemaName = null)
        {
            return _generateCode(schemaName, viewName, true);
        }

        private string _generateCode(string tableName, string schemaName, bool isView = false)
        {
            var objName = db.ParseObjectName(tableName, schemaName);
            tableName = objName.Name;
            schemaName = objName.SchemaName;
            var code = "";
            SqlServerTable table = null;
            SqlServerView view = null;
            if (isView)
            {
                view = ShowViewDetail(tableName, schemaName) as SqlServerView;
            }
            else
            {
                table = ShowTableDetail(tableName, schemaName) as SqlServerTable;
            }
            var name = (isView ? view.Name : table.Name);
            code += $@"
using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;
using DotNetCommon.Serialize; // dotnet add package DotNetCommon
public class {name}
{{
        public const string TABLE_NAME = ""{name}"";
        public const string KEY_NAME = ""{table?.PrimaryKey}"";
        private static List<string> allColumns;
        static {name}()
        {{
            allColumns = new List<string>();
            var type = typeof({name});
            var props = type.GetProperties().ToList();
            props.ForEach(prop => allColumns.Add(prop.Name));
        }}
        public static List<string> AllColumns => allColumns;
";
            var columns = isView ? view.Columns : table.Columns;
            foreach (var item in columns)
            {
                var col = item as SqlServerColumn;
                var type = "";
                switch (col.Type)
                {
                    case EnumSqlServerColumnType.Bit: type = "bool"; break;

                    case EnumSqlServerColumnType.TinyInt: type = "sbyte"; break;
                    case EnumSqlServerColumnType.SmallInt: type = "short"; break;
                    case EnumSqlServerColumnType.Int: type = "int"; break;
                    case EnumSqlServerColumnType.BigInt: type = "long"; break;

                    case EnumSqlServerColumnType.Float:
                    case EnumSqlServerColumnType.Real:
                        type = "double";
                        break;

                    case EnumSqlServerColumnType.Numeric:
                    case EnumSqlServerColumnType.Decimal:
                        type = "decimal";
                        break;

                    case EnumSqlServerColumnType.SmallMoney:
                    case EnumSqlServerColumnType.Money:
                        type = "decimal";
                        break;

                    case EnumSqlServerColumnType.Char:
                    case EnumSqlServerColumnType.VarChar:
                    case EnumSqlServerColumnType.NChar:
                    case EnumSqlServerColumnType.NVarChar:
                    case EnumSqlServerColumnType.Text:
                    case EnumSqlServerColumnType.NText:
                        type = "string";
                        break;

                    case EnumSqlServerColumnType.Binary:
                    case EnumSqlServerColumnType.Image:
                    case EnumSqlServerColumnType.VarBinary:
                        type = "byte[]";
                        break;

                    case EnumSqlServerColumnType.Date:
                    case EnumSqlServerColumnType.DateTime:
                    case EnumSqlServerColumnType.DateTime2:
                    case EnumSqlServerColumnType.DateTimeOffset:
                    case EnumSqlServerColumnType.SmallDateTime:
                    case EnumSqlServerColumnType.Time:
                        type = "DateTime";
                        break;

                    case EnumSqlServerColumnType.TimeStamp:
                        type = "byte[]";
                        break;

                    case EnumSqlServerColumnType.Geography: type = "Microsoft.SqlServer.Types.SqlGeography"; break;
                    case EnumSqlServerColumnType.Geometry: type = "Microsoft.SqlServer.Types.SqlGeometry"; break;
                    case EnumSqlServerColumnType.Hierarchyid: type = "Microsoft.SqlServer.Types.SqlHierarchyId"; break;
                    case EnumSqlServerColumnType.UniqueIdentifier: type = "Guid"; break;
                    case EnumSqlServerColumnType.SqlVariant: type = "object"; break;
                    case EnumSqlServerColumnType.Xml: type = "string"; break;
                    case EnumSqlServerColumnType.Other: type = "object"; break;
                    default:
                        break;
                }
                if (col.IsNullAble)
                {
                    if (new string[] { "object", "byte[]", "string" }.Contains(type)) { }
                    else
                    {
                        type += "?";
                    }
                }
                var desc = col.Desc;
                desc = desc.Replace("\r\n", " ");
                if (type == "long")
                {
                    code += @$"

        /// <summary>
        /// {desc}
        /// </summary>
        [JsonConverter(typeof(NumberConverter), NumberConverterShip.Int64)]
        public {type} {col.Name} {{ set; get; }}";
                }
                else
                {
                    code += @$"

        /// <summary>
        /// {desc}
        /// </summary>
        public {type} {col.Name} {{ get; set; }}";
                }

            }
            code += "\r\n}";
            return code;
        }
        #endregion

        #region 生成insert语句 GenerateInsertSql/GenerateInsertSqlFile
        /// <summary>
        /// 根据表名称和过滤条件生成表数据的insert语句(时间戳列[timestamp]不会参与生成)
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="tableName">表名称</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public override (long count, string sql, long total) GenerateInsertSql(string schemaName, string tableName, int limitCount = 0, string filter = null, IDictionary<string, object> parameters = null)
        {
            var objName = db.ParseObjectName(tableName, schemaName);
            if (string.IsNullOrWhiteSpace(filter)) filter = "";
            var res = "";
            var table = ShowTableDetail(objName.Name, objName.SchemaName);
            res += $"print '{objName.NormalNameQuoted}: 开始执行insert语句'\r\n";
            var isIdentity = table.Columns.Find(col => col.IsIdentity) != null;
            if (isIdentity)
            {
                res += $"set IDENTITY_INSERT {objName.NormalNameQuoted} ON\r\n";
            }
            var insertSql = "";
            var colNames = table.Columns.Where(col => col.TypeString != "timestamp").Select(col => $"[{col.Name}]").ToList();
            insertSql = $"insert into {objName.NormalNameQuoted}({string.Join(",", colNames)}) ";
            var count = 0;
            db.SelectDataReader(reader =>
            {
                while (reader.RawReader.Read())
                {
                    var sql = insertSql + " values(";
                    var values = new List<string>();
                    foreach (var col in table.Columns)
                    {
                        if (col.TypeString == "timestamp") continue;
                        var value = reader.RawReader[col.Name];
                        if (value == DBNull.Value)
                        {
                            values.Add("NULL");
                        }
                        else if (value is string)
                        {
                            values.Add("'" + value.ToString().Replace("'", "''") + "'");
                        }
                        else if (value is byte[])
                        {
                            var bs = (byte[])value;
                            var sb = new StringBuilder();
                            sb.Append("0x");
                            foreach (var by in bs)
                            {
                                sb.Append(by.ToString("X2"));
                            }
                            values.Add(sb.ToString());
                        }
                        else if (value is DateTime || value is DateTimeOffset || value is TimeSpan)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is Guid)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeography")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geography::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeometry")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geometry::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlHierarchyId")
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is bool)
                        {
                            var t = (bool)value;
                            values.Add(t ? "1" : "0");
                        }
                        else
                        {
                            values.Add(value.ToString().Replace("'", "''"));
                        }
                    }
                    sql += $"{string.Join(",", values)});";
                    res += $"\r\n{sql}";
                    count++;
                    if (limitCount > 0 && limitCount == count) break;
                };
            }, $"select * from {objName.NormalNameQuoted} where 1=1 {filter}", parameters);

            if (isIdentity)
            {
                res += $"\r\n\r\nset IDENTITY_INSERT {objName.NormalNameQuoted} OFF\r\n";
            }
            res += $"\r\nprint '{objName.NormalNameQuoted}: 共执行了{count}次insert(自行判断执行成功或失败)'";
            var total = GetCount(tableName, schemaName);
            return (count, res, total);
        }

        /// <summary>
        /// 根据视图名称和过滤条件生成视图数据的insert语句(时间戳列[timestamp]不会参与生成)
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="viewName">视图名称</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public (long count, string sql, long total) GenerateInsertViewSql(string schemaName, string viewName, int limitCount = 0, string filter = null, IDictionary<string, object> parameters = null)
        {
            var objName = db.ParseObjectName(viewName, schemaName);
            if (string.IsNullOrWhiteSpace(filter)) filter = "";
            var res = "";
            var table = ShowViewDetail(viewName, schemaName);
            res += $"print '{objName.NormalNameQuoted}: 开始执行insert语句'\r\n";
            var insertSql = "";
            var colNames = table.Columns.Where(col => col.TypeString != "timestamp").Select(col => $"[{col.Name}]").ToList();
            insertSql = $"insert into {objName.NormalNameQuoted}({string.Join(",", colNames)}) ";
            var count = 0;
            db.SelectDataReader(reader =>
            {
                while (reader.RawReader.Read())
                {
                    var sql = insertSql + " values(";
                    var values = new List<string>();
                    foreach (var col in table.Columns)
                    {
                        if (col.TypeString == "timestamp") continue;
                        var value = reader.RawReader[col.Name];
                        if (value == DBNull.Value)
                        {
                            values.Add("NULL");
                        }
                        else if (value is string)
                        {
                            values.Add("'" + value.ToString().Replace("'", "''") + "'");
                        }
                        else if (value is byte[])
                        {
                            var bs = (byte[])value;
                            var sb = new StringBuilder();
                            sb.Append("0x");
                            foreach (var by in bs)
                            {
                                sb.Append(by.ToString("X2"));
                            }
                            values.Add(sb.ToString());
                        }
                        else if (value is DateTime || value is DateTimeOffset || value is TimeSpan)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is Guid)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeography")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geography::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeometry")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geometry::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlHierarchyId")
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is bool)
                        {
                            var t = (bool)value;
                            values.Add(t ? "1" : "0");
                        }
                        else
                        {
                            values.Add(value.ToString().Replace("'", "''"));
                        }
                    }
                    sql += $"{string.Join(",", values)});";
                    res += $"\r\n{sql}";
                    count++;
                    if (limitCount > 0 && limitCount == count) break;
                };
            }, $"select * from {objName.NormalNameQuoted} where 1=1 {filter}", parameters);

            res += $"\r\nprint '{objName.NormalNameQuoted}: 共执行了{count}次insert(自行判断执行成功或失败)'";
            var total = GetCount(viewName, schemaName);
            return (count, res, total);
        }

        /// <summary>
        /// 将表数据全部导出到文件
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="tableName">表名</param>
        /// <param name="fileAbsPath">文件的绝对路径</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="parameters">参数</param>
        /// <returns>导出的记录数</returns>
        public override long GenerateInsertSqlFile(string schemaName, string tableName, string fileAbsPath, int limitCount = 0, string filter = null, IDictionary<string, object> parameters = null)
        {
            var objName = db.ParseObjectName(tableName, schemaName);
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var table = ShowTableDetail(tableName, schemaName);
            File.AppendAllText(fileAbsPath, $"print '{objName.NormalNameQuoted}: 开始执行insert语句'");
            var isIdentity = table.Columns.Find(col => col.IsIdentity) != null;
            if (isIdentity)
            {
                File.AppendAllText(fileAbsPath, $"set IDENTITY_INSERT {objName.NormalNameQuoted} ON\r\n");
            }
            var insertSql = "";
            var colNames = table.Columns.Where(col => col.TypeString != "timestamp").Select(col => $"[{col.Name}]").ToList();
            insertSql = $"insert into {objName.NormalNameQuoted}({string.Join(",", colNames)}) ";
            var count = 0L;
            var countLimit = 0;
            db.SelectDataReader(reader =>
            {
                while (reader.RawReader.Read())
                {
                    var sql = insertSql + " values(";
                    var values = new List<string>();
                    foreach (var col in table.Columns)
                    {
                        if (col.TypeString == "timestamp") continue;
                        var value = reader.RawReader[col.Name];
                        if (value == DBNull.Value)
                        {
                            values.Add("NULL");
                        }
                        else if (value is string)
                        {
                            values.Add("'" + value.ToString().Replace("'", "''") + "'");
                        }
                        else if (value is byte[])
                        {
                            var bs = (byte[])value;
                            var sb = new StringBuilder();
                            sb.Append("0x");
                            foreach (var by in bs)
                            {
                                sb.Append(by.ToString("X2"));
                            }
                            values.Add(sb.ToString());
                        }
                        else if (value is DateTime || value is DateTimeOffset || value is TimeSpan)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is Guid)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeography")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geography::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeometry")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geometry::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlHierarchyId")
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is bool)
                        {
                            var t = (bool)value;
                            values.Add(t ? "1" : "0");
                        }
                        else
                        {
                            values.Add(value.ToString().Replace("'", "''"));
                        }
                    }
                    sql += $"{string.Join(",", values)});";
                    File.AppendAllText(fileAbsPath, $"\r\n{sql}");
                    count++;
                    countLimit++;
                    if (limitCount > 0 && limitCount == countLimit) break;
                };
            }, $"select * from {objName.NormalNameQuoted} where 1=1 {filter}", parameters);

            if (isIdentity)
            {
                File.AppendAllText(fileAbsPath, $"\r\n\r\nset IDENTITY_INSERT {objName.NormalNameQuoted} OFF\r\n");
            }
            File.AppendAllText(fileAbsPath, $"\r\nprint '{objName.NormalNameQuoted}: 共执行了{count}次insert(自行判断执行成功或失败)'");
            return count;
        }

        /// <summary>
        /// 将表视图数据全部导出到文件
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="viewName">视图名</param>
        /// <param name="fileAbsPath">文件的绝对路径</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="parameters">参数</param>
        /// <returns>导出的记录数</returns>
        public long GenerateInsertViewSqlFile(string schemaName, string viewName, string fileAbsPath, int limitCount = 0, string filter = null, IDictionary<string, object> parameters = null)
        {
            var objName = db.ParseObjectName(viewName, schemaName);
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var table = ShowTableDetail(objName.Name, objName.SchemaName);
            File.AppendAllText(fileAbsPath, $"print '{objName.NormalNameQuoted}: 开始执行insert语句'");
            var insertSql = "";
            var colNames = table.Columns.Where(col => col.TypeString != "timestamp").Select(col => $"[{col.Name}]").ToList();
            insertSql = $"insert into {objName.NormalNameQuoted}({string.Join(",", colNames)}) ";
            var count = 0L;
            var countLimit = 0;
            db.SelectDataReader(reader =>
            {
                while (reader.RawReader.Read())
                {
                    var sql = insertSql + " values(";
                    var values = new List<string>();
                    foreach (var col in table.Columns)
                    {
                        if (col.TypeString == "timestamp") continue;
                        var value = reader.RawReader[col.Name];
                        if (value == DBNull.Value)
                        {
                            values.Add("NULL");
                        }
                        else if (value is string)
                        {
                            values.Add("'" + value.ToString().Replace("'", "''") + "'");
                        }
                        else if (value is byte[])
                        {
                            var bs = (byte[])value;
                            var sb = new StringBuilder();
                            sb.Append("0x");
                            foreach (var by in bs)
                            {
                                sb.Append(by.ToString("X2"));
                            }
                            values.Add(sb.ToString());
                        }
                        else if (value is DateTime || value is DateTimeOffset || value is TimeSpan)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is Guid)
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeography")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geography::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeometry")
                        {
                            var prop = value.GetType().GetProperty("STSrid");
                            var grid = prop.GetValue(value);
                            values.Add($"geometry::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                        }
                        else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlHierarchyId")
                        {
                            values.Add("'" + value.ToString() + "'");
                        }
                        else if (value is bool)
                        {
                            var t = (bool)value;
                            values.Add(t ? "1" : "0");
                        }
                        else
                        {
                            values.Add(value.ToString().Replace("'", "''"));
                        }
                    }
                    sql += $"{string.Join(",", values)});";
                    File.AppendAllText(fileAbsPath, $"\r\n{sql}");
                    count++;
                    countLimit++;
                    if (limitCount > 0 && limitCount == countLimit) break;
                };
            }, $"select * from {objName.NormalNameQuoted} where 1=1 {filter}", parameters);
            File.AppendAllText(fileAbsPath, $"\r\nprint '{objName.NormalNameQuoted}: 共执行了{count}次insert(自行判断执行成功或失败)'");
            return count;
        }
        #endregion

        #region 生成建表脚本

        private string GenerateCreateTableSql(Table detail, bool includeForeignKey = true)
        {
            var schemaName = detail.SchemaName;
            var tableName = detail.Name;
            //生成create table
            var sql = $@"IF (OBJECT_ID('[{schemaName}].[{tableName}]') is not null)
begin
    drop table [{schemaName}].[{tableName}];
    print '[{schemaName}].[{tableName}]: 已删除旧表';
end
GO
create table [{schemaName}].[{tableName}](";
            var isUnionKey = detail.PrimaryKeyColumns.Count > 1;
            foreach (var item in detail.Columns)
            {
                var col = item as SqlServerColumn;
                sql += $"\r\n    [{col.Name}] {col.TypeString}";
                if (col.IsIdentity)
                {
                    sql += $" identity({col.IdentityStart},{col.IdentityIncre})";
                }
                if (col.IsPrimaryKey & !isUnionKey)
                {
                    //单列主键
                    sql += $" primary key,";
                    continue;
                }
                if (col.IsComputed)
                {
                    sql += $" as {col.ComputedDefinition}";
                    if (col.IsPersisted.Value) sql += " PERSISTED";
                }
                else
                {
                    if (!col.IsNullAble)
                    {
                        sql += $" not null";
                    }
                    if (col.IsUnique)
                    {
                        sql += " unique";
                    }
                    if (col.HasDefault)
                    {
                        sql += $" default({col.Default})";
                    }
                }
                sql += ",";
            }
            sql = sql.TrimEnd(',');
            sql += "\r\n);";
            sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已创建新表';";
            sql += "\r\nGO";
            //生成表注释
            if (!string.IsNullOrWhiteSpace(detail.Desc))
            {
                sql += $"\r\nEXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc.Replace("'", "''")}' , @level0type = N'SCHEMA',@level0name = '{schemaName}', @level1type = N'TABLE',@level1name = N'{tableName}', @level2type = null,@level2name = null;";
                sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已添加表说明';";
            }
            //生成字段注释
            var b = false;
            foreach (var col in detail.Columns)
            {
                if (!string.IsNullOrWhiteSpace(col.Desc))
                {
                    sql += $"\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{col.Desc.Replace("'", "''")}' , @level0type=N'SCHEMA',@level0name='{schemaName}', @level1type=N'TABLE',@level1name=N'{tableName}', @level2type=N'COLUMN',@level2name=N'{col.Name}'";
                    b = true;
                }
            }
            if (b)
            {
                sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已添加列说明';";
            }
            //生成约束
            b = false;
            foreach (var item in detail.Constraints)
            {
                var constraint = item as SqlServerConstraint;
                if (constraint.Type == EnumConstraintType.Check)
                {
                    //检查约束
                    sql += $"\r\nALTER TABLE [{schemaName}].[{tableName}] ADD CONSTRAINT {constraint.Name} CHECK({constraint.Definition});";
                    b = true;
                }
                else if (constraint.Type == EnumConstraintType.DefaultValue)
                {
                    //默认约束定义在列上
                }
                else if (constraint.Type == EnumConstraintType.ForeignKey)
                {
                    if (includeForeignKey)
                    {
                        //外键约束
                        var detail2 = ShowTableDetail(constraint.TableName, constraint.SchemaName);
                        sql += $"\r\nALTER TABLE [{schemaName}].[{tableName}] ADD CONSTRAINT {constraint.Name} FOREIGN KEY({constraint.Constraintkeys}) REFERENCES [{schemaName}].[{constraint.ReferenceTableName}]({constraint.ReferenceColumnName}) ON DELETE {constraint.Delete_Action.Replace("_", " ")} on UPDATE {constraint.Update_Action.Replace("_", " ")};";
                        b = true;
                    }
                }
                else if (constraint.Type == EnumConstraintType.PrimaryKey)
                {
                    //主键约束, 联合主键写在这
                    if (detail.PrimaryKeyColumns.Count > 1)
                    {
                        sql += $"\r\nALTER TABLE [{schemaName}].[{tableName}] ADD CONSTRAINT {constraint.Name} primary key ({constraint.Constraintkeys});";
                        b = true;
                    }
                }
                else if (constraint.Type == EnumConstraintType.Unique)
                {
                    //唯一约束,联合唯一写在这
                    if (constraint.Constraintkeys.Contains(","))
                    {
                        sql += $"\r\nALTER TABLE [{schemaName}].[{tableName}] ADD CONSTRAINT {constraint.Name} UNIQUE ({constraint.Constraintkeys});";
                        b = true;
                    }
                }
            }
            if (b)
            {
                sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已完成约束';";
            }

            //生成索引
            b = false;
            foreach (var item in detail.Indexes)
            {
                var index = item as SqlServerIndex;
                //Heap类型索引不用单独建立
                if (index.IndexType == "HEAP") continue;
                if (index.IsUniqueKey || index.IsPrimaryKey)
                {
                    //唯一或主键自动生成的,这里不用额外加索引
                    continue;
                }
                var clust = index.IsClustered ? "CLUSTERED" : "NONCLUSTERED";
                var unique = index.IsUnique ? "UNIQUE" : "";
                sql += $"\r\ncreate {unique} {clust} INDEX {index.Name} ON [{schemaName}].[{tableName}]({index.ColumnNames});";
                b = true;
            }
            if (b)
            {
                sql += $"\r\nprint '[{schemaName}].[{tableName}]: 已完成索引';";
            }

            //生成触发器
            b = false;
            foreach (var item in detail.Triggers)
            {
                var trigger = item as SqlServerTrigger;
                sql += $"\r\nGO\r\n{trigger.CreateSql}";
                b = true;
            }
            if (b)
            {
                sql += $"\r\nGO\r\nprint '[{schemaName}].[{tableName}]: 已完成触发器';";
            }

            return sql;
        }
        /// <summary>
        /// 生成建表脚本
        /// </summary>
        /// <param name="schemaName">模式名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override string GenerateCreateTableSql(string tableName, string schemaName = null)
        {
            var objName = db.ParseObjectName(tableName, schemaName);
            var detail = ShowTableDetail(tableName, schemaName);
            var builder = new StringBuilder();
            //先检查schema
            if (detail.SchemaName != "dbo")
            {
                //含有dbo意外的schema
                builder.Append($"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                builder.Append($@"if((select 1 from sys.schemas where name='{detail.SchemaName}') is null)
begin
    exec ('create schema {detail.SchemaName}');
    print '新建schema: {detail.SchemaName}';
end
GO");
                builder.Append($"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            var tableSql = GenerateCreateTableSql(detail);
            builder.Append(tableSql);
            return builder.ToString();
        }

        /// <summary>
        /// 生成建视图语句
        /// </summary>
        /// <param name="schemaName"></param>
        /// <param name="viewName"></param>
        /// <returns></returns>
        public string GenerateCreateViewSql(string viewName, string schemaName = null)
        {
            var objName = db.ParseObjectName(viewName, schemaName);
            var detail = ShowViewDetail(viewName, schemaName) as SqlServerView;
            var sqlbuilder = new StringBuilder();
            sqlbuilder.AppendLine($@"
IF (OBJECT_ID('{objName.NormalName}') is not null)
begin
    drop view {objName.NormalNameQuoted};
    print '{objName.NormalNameQuoted}: 已删除旧视图';
end
GO
");
            sqlbuilder.AppendLine(detail.CreateSql.Trim());
            sqlbuilder.AppendLine($@"
GO
print '{objName.NormalNameQuoted}: 已创建新视图';
GO");
            if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
            {
                sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'view',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                sqlbuilder.AppendLine($"print '{objName.NormalNameQuoted}: 已添加视图说明';");
            }
            var b = false;
            detail.Columns.ForEach(column =>
            {
                if (column.Desc.IsNotNullOrEmptyOrWhiteSpace())
                {
                    sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{column.Desc}' , @level0type=N'SCHEMA',@level0name='{detail.SchemaName}', @level1type=N'view',@level1name=N'{detail.Name}', @level2type=N'COLUMN',@level2name=N'{column.Name}'");
                    b = true;
                }
            });
            if (b)
            {
                sqlbuilder.AppendLine($"print '{objName.NormalNameQuoted}: 已添加列说明';");
            }
            return sqlbuilder.ToString();

        }

        /// <summary>
        /// 生成建序列语句
        /// </summary>
        /// <param name="schemaName"></param>
        /// <param name="seqName"></param>
        /// <returns></returns>
        public string GenerateCreateSeqSql(string seqName, string schemaName = null)
        {
            var objName = db.ParseObjectName(seqName, schemaName);
            var detail = _showSequences(objName.Name, objName.SchemaName).FirstOrDefault() as SqlServerSequence;
            var sqlbuilder = new StringBuilder();
            sqlbuilder.AppendLine($@"
IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop SEQUENCE [{detail.SchemaName}].{detail.Name};
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧序列';
end
GO
");
            sqlbuilder.AppendLine(detail.CreateSql.Trim());
            sqlbuilder.AppendLine($@"
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新序列';
GO");
            return sqlbuilder.ToString();

        }

        /// <summary>
        /// 生成建存储过程语句
        /// </summary>
        /// <param name="schemaName"></param>
        /// <param name="procName"></param>
        /// <returns></returns>
        public string GenerateCreateProcSql(string procName, string schemaName = null)
        {
            var objName = db.ParseObjectName(procName, schemaName);
            var detail = _showProcedures(procName, schemaName).FirstOrDefault() as SqlServerProcedure;
            var sqlbuilder = new StringBuilder();
            sqlbuilder.AppendLine($@"
IF (OBJECT_ID('{objName.NormalName}') is not null)
begin
    drop proc {objName.NormalNameQuoted};
    print '{objName.NormalNameQuoted}: 已删除旧存储过程';
end
GO");
            sqlbuilder.AppendLine(detail.CreateSql.Trim());
            sqlbuilder.AppendLine($@"GO
print '{objName.NormalNameQuoted}: 已创建新存储过程';
GO");
            if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
            {
                sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'PROCEDURE',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                sqlbuilder.AppendLine($"print '{objName.NormalNameQuoted}: 已添加存储过程说明';");
            }
            return sqlbuilder.ToString();
        }

        /// <summary>
        /// 生成建函数语句
        /// </summary>
        /// <param name="schemaName"></param>
        /// <param name="funcName"></param>
        /// <returns></returns>
        public string GenerateCreateFuncSql(string funcName, string schemaName = null)
        {
            var objName = db.ParseObjectName(funcName, schemaName);
            var detail = _showFunctions(objName.Name, objName.SchemaName).FirstOrDefault() as SqlServerFunction;
            var sqlbuilder = new StringBuilder();
            sqlbuilder.AppendLine($@"
IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop function [{detail.SchemaName}].{detail.Name};
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧函数';
end
GO");
            sqlbuilder.AppendLine(detail.CreateSql.Trim());
            sqlbuilder.AppendLine($@"GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新函数';
GO");
            if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
            {
                sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'FUNCTION',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加函数说明';");
            }
            return sqlbuilder.ToString();
        }

        /// <summary>
        /// 生成建表脚本文件
        /// </summary>
        /// <param name="tables">表名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <param name="includeInsertSql">是否包含insert脚本</param>
        /// <returns></returns>
        public override void GenerateCreateTableSqlFile(List<Table> tables, string fileAbsPath, bool includeInsertSql = false)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = tables.Select(t => ShowTableDetail(t.Name, t.SchemaName)).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo以外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //如果有外键,先删除外键
            if (details.Any(db => db.Constraints.Any(c => c.Type == EnumConstraintType.ForeignKey)))
            {
                File.AppendAllText(fileAbsPath, $"--------------------------发现外键约束,先删除" + "\r\n");
                File.AppendAllText(fileAbsPath, $"print '发现外键约束,先删除';" + "\r\n");
                //先删除外键约束
                var count = 0;
                details.ForEach(i =>
                {
                    var cons = i.Constraints.Where(i => i.Type == EnumConstraintType.ForeignKey).ToList();
                    cons.ForEach(con =>
                    {
                        var dropCons = $@"IF (OBJECT_ID('{con.SchemaName}.{con.Name}') is not null)
begin
    alter table [{i.SchemaName}].[{i.Name}] drop CONSTRAINT {con.Name};
    print '[{i.SchemaName}].[{i.Name}]: 已删除约束:{con.Name}';
end
GO";
                        File.AppendAllText(fileAbsPath, dropCons + "\r\n");
                        count++;
                    });
                });
                File.AppendAllText(fileAbsPath, $"--------------------------已删除外键约束,开始建表" + "\r\n");
                File.AppendAllText(fileAbsPath, $"print '已删除{count}个外键约束,开始建表...';" + "\r\n\r\n");
            }
            //生成脚本,不包含外键约束
            foreach (var detail in details)
            {
                File.AppendAllText(fileAbsPath, $"--------------------------表:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = GenerateCreateTableSql(detail, false);
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建表结束,共建表:{details.Count}个---------------------" + "\r\n\r\n");
            //插入数据
            if (includeInsertSql)
            {
                File.AppendAllText(fileAbsPath, $"\r\n--------------------------开始插入数据" + "\r\n");
                details.ForEach(detail =>
                {
                    File.AppendAllText(fileAbsPath, $"--------------------------插入到表:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                    var (count, sqlInsert, total) = GenerateInsertSql(detail.SchemaName, detail.Name);
                    File.AppendAllText(fileAbsPath, sqlInsert + "\r\n");
                    File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------------------\r\n");
                });
            }
            //最后创建外键约束
            if (details.Any(db => db.Constraints.Any(c => c.Type == EnumConstraintType.ForeignKey)))
            {
                File.AppendAllText(fileAbsPath, $"---------------------------最后,追加外键约束" + "\r\n");
                File.AppendAllText(fileAbsPath, $"print '最后,追加外键约束';" + "\r\n");
                //外键约束
                var count = 0;
                details.ForEach(detail =>
                {
                    var cons = detail.Constraints.Where(i => i.Type == EnumConstraintType.ForeignKey).ToList();
                    cons.ForEach(constraint =>
                    {
                        var detail2 = ShowTableDetail(constraint.TableName, constraint.SchemaName);
                        var sql = $"\r\nALTER TABLE [{detail.SchemaName}].[{detail.Name}] ADD CONSTRAINT {constraint.Name} FOREIGN KEY({constraint.Constraintkeys}) REFERENCES [{detail.SchemaName}].[{constraint.ReferenceTableName}]({constraint.ReferenceColumnName}) ON DELETE {constraint.Delete_Action.Replace("_", " ")} on UPDATE {constraint.Update_Action.Replace("_", " ")};";
                        File.AppendAllText(fileAbsPath, sql + "\r\n");
                        count++;
                    });
                });
                File.AppendAllText(fileAbsPath, $"---------------------------已完成外键约束追加" + "\r\n");
                File.AppendAllText(fileAbsPath, $"print '已完成外键约束追加,共追加了{count}条外键约束';" + "\r\n\r\n");
            }

            return;
        }

        /// <summary>
        /// 生成建视图脚本文件
        /// </summary>
        /// <param name="views">视图名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <returns></returns>
        public void GenerateCreateViewSqlFile(List<View> views, string fileAbsPath)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = views.Select(t => ShowViewDetail(t.Name, t.SchemaName) as SqlServerView).OrderBy(i => i.CreateTime).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo意外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //生成脚本
            foreach (var detail in details)
            {
                var sqlbuilder = new StringBuilder();
                if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
                {
                    sqlbuilder.AppendLine("GO");
                    sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'view',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                    sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加视图说明';");
                }
                File.AppendAllText(fileAbsPath, $"--------------------------视图:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = $@"IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop view [{detail.SchemaName}].[{detail.Name}];
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧视图';
end
GO
{detail.CreateSql.Trim()}
{sqlbuilder.ToString().Trim()}
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新视图';
GO";
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建视图结束,共建视图:{details.Count}个---------------------" + "\r\n\r\n");
        }

        /// <summary>
        /// 生成建函数脚本文件
        /// </summary>
        /// <param name="funcs">函数名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <returns></returns>
        public void GenerateCreateFuncSqlFile(List<Function> funcs, string fileAbsPath)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = funcs.Select(t => _showFunctions(t.Name, t.SchemaName).FirstOrDefault() as SqlServerFunction).OrderBy(i => i.CreateTime).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo意外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //生成脚本
            foreach (var detail in details)
            {
                var sqlbuilder = new StringBuilder();
                if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
                {
                    sqlbuilder.AppendLine("GO");
                    sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'FUNCTION',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                    sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加函数说明';");
                }
                File.AppendAllText(fileAbsPath, $"--------------------------函数:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = $@"IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop function [{detail.SchemaName}].[{detail.Name}];
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧函数';
end
GO
{detail.CreateSql.Trim()}
{sqlbuilder.ToString().Trim()}
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新函数';
GO";
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建函数结束,共建函数:{details.Count}个---------------------" + "\r\n\r\n");
        }

        /// <summary>
        /// 生成建存储过程脚本文件
        /// </summary>
        /// <param name="procs">存储过程名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <returns></returns>
        public void GenerateCreateProcSqlFile(List<Procedure> procs, string fileAbsPath)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = procs.Select(t => _showProcedures(t.Name, t.SchemaName).FirstOrDefault() as SqlServerProcedure).OrderBy(i => i.CreateTime).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo意外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //生成脚本
            foreach (var detail in details)
            {
                var sqlbuilder = new StringBuilder();
                if (detail.Desc.IsNotNullOrEmptyOrWhiteSpace())
                {
                    sqlbuilder.AppendLine("GO");
                    sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'{detail.Desc}' , @level0type = N'SCHEMA',@level0name = '{detail.SchemaName}', @level1type = N'PROCEDURE',@level1name = N'{detail.Name}', @level2type = null,@level2name = null;");
                    sqlbuilder.AppendLine($"print '[{detail.SchemaName}].[{detail.Name}]: 已添加存储过程说明';");
                }
                File.AppendAllText(fileAbsPath, $"--------------------------存储过程:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = $@"IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop PROCEDURE [{detail.SchemaName}].[{detail.Name}];
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧存储过程';
end
GO
{detail.CreateSql.Trim()}
{sqlbuilder.ToString().Trim()}
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新存储过程';
GO";
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建存储过程结束,共建存储过程:{details.Count}个---------------------" + "\r\n\r\n");
        }

        /// <summary>
        /// 生成建序列脚本文件
        /// </summary>
        /// <param name="seqs">视图名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <returns></returns>
        public void GenerateCreateSeqSqlFile(List<Sequence> seqs, string fileAbsPath)
        {
            var dir = Path.GetDirectoryName(fileAbsPath);
            if (!Directory.Exists(dir))
            {
                lock (typeof(SqlServerManage))
                {
                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }
                }
            }
            var details = seqs.Select(t => _showSequences(t.Name, t.SchemaName).FirstOrDefault() as SqlServerSequence).OrderBy(i => i.CreateTime).ToList();
            //先检查schema
            var schemas = details.Select(i => i.SchemaName).Where(i => i != "dbo").Distinct().ToList();
            if (schemas.Count > 0)
            {
                //含有dbo意外的schema
                File.AppendAllText(fileAbsPath, $"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
                var schemas_existed = ShowSchemas().Select(i => i.Name).ToList();
                schemas.ForEach(schema =>
                {
                    File.AppendAllText(fileAbsPath, $@"if((select 1 from sys.schemas where name='{schema}') is null)
begin
	exec ('create schema {schema}');
    print '新建schema: {schema}';
end
GO");
                });
                File.AppendAllText(fileAbsPath, $"\r\n------------------------------------------------------------------" + "\r\n\r\n");
            }
            //生成脚本
            foreach (var detail in details)
            {
                File.AppendAllText(fileAbsPath, $"--------------------------序列:[{detail.SchemaName}].[{detail.Name}]" + "\r\n");
                var sql = $@"IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop SEQUENCE [{detail.SchemaName}].[{detail.Name}];
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧序列';
end
GO
{detail.CreateSql.Trim()}
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新序列';
GO";
                File.AppendAllText(fileAbsPath, sql + "\r\n");
                File.AppendAllText(fileAbsPath, $"------------------------------------------------------------------" + "\r\n");
            }
            File.AppendAllText(fileAbsPath, $"--------------------------建序列结束,共建序列:{details.Count}个---------------------" + "\r\n\r\n");
        }
        #endregion
    }
}
